package com.neusoft.excel.service.impl;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import com.neusoft.excel.common.MyException;
import com.neusoft.excel.entity.User;
import com.neusoft.excel.dao.UserDao;
import com.neusoft.excel.service.ImportExcelService;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;




@Service
@Transactional(rollbackFor = Exception.class)
public class ImportExcelServiceImpl implements ImportExcelService {

    private static Logger logger = Logger.getLogger("ImportExcelServiceImpl");

    @Autowired
    private UserDao userMapper;

    @Override
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
        boolean notNull = false;
        List<User> userList = new ArrayList<User>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new MyException("上传文件格式不正确");
        }

        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        User user;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }

            user = new User();

            String stu = row.getCell(0).getStringCellValue();

            row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            String name = row.getCell(1).getStringCellValue();

            String add = row.getCell(2).getStringCellValue();

            String des = row.getCell(3).getStringCellValue();

            user.setStu(stu);
            user.setName(name);
            user.setAddress(add);
            user.setDes(des);

            userList.add(user);
        }
        for (User userResord : userList) {
            String stu = userResord.getStu();
            int cnt = userMapper.selectByName(stu);
            if (cnt == 0) {
                userMapper.addUser(userResord);
                logger.info(" 插入 "+userResord);
            } else {
                userMapper.updateUserByName(userResord);
                logger.info(" 更新 "+userResord);
            }
        }
        return notNull;
    }
}
